

USE [QLLVANTAI]
GO

/****** Object:  StoredProcedure [dbo].[sp_DKLichlamviec]    Script Date: 09/30/2013 23:59:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

if exists(Select * from sysobjects where name = 'sp_DKLichlamviec')
begin
drop proc sp_DKLichlamviec
end
go
CREATE proc [dbo].[sp_DKLichlamviec] 
		@mavanchuyen int, @maxe int , @manhanvien int, @vaitro int, @error nvarchar(100) out
as
begin tran
	
	-- kiem tra mavanchuyen
	declare @kt_mavc int
	
	set @kt_mavc = (select COUNT(*) from LichVanChuyen where Ma_VanChuyen = @mavanchuyen)
	if @kt_mavc = 0
	begin
		set @error = N'Lỗi: Mã vận chuyển không tồn tại'
		rollback
		print @error 
		return
	end	
	
	-- kiem tra maxe
	declare @kt_maxe int
	
	set @kt_maxe = (select COUNT(*) from ChiTietLich where Ma_VanChuyen = @mavanchuyen and Ma_Xe = @maxe ) 
	if @kt_maxe = 0
	begin
		set @error = N'Lỗi: Mã xe không tồn tại' 
		rollback
		print @error 
		return
	end	
	
	-- kiem tra nhan vien da dang ky chuyen di
	if exists(select * from DangKy where Ma_VanChuyen = @mavanchuyen  and Ma_NV = @manhanvien)
	begin
		set @error = N'Lỗi: bạn đã đăng ký vào chuyến đi này' 
		rollback
		print @error 
		return
	end 
	
	
	if @vaitro = 1  -- taixe
	begin
		
		
		declare @sl_taixetrong int
		set @sl_taixetrong = (select So_TaiXeTrong  from ChiTietLich where Ma_VanChuyen = @mavanchuyen and Ma_Xe = @maxe)
		
		if @sl_taixetrong < 1
		begin
			set @error = N'Lỗi: đã đủ người đăng ký'
			rollback
			print @error 
		end
		
		
		else
		begin
			waitfor delay '00:00:05'
			
			set @sl_taixetrong = @sl_taixetrong - 1
			-- tien hanh dang ky cho nhan vien do
			insert into DangKy (Ma_VanChuyen, Ma_NV, VaiTro, Ma_Xe) values(@mavanchuyen, @manhanvien, @vaitro, @maxe)
			update ChiTietLich set So_TaiXeTrong = @sl_taixetrong where Ma_VanChuyen = @mavanchuyen and Ma_Xe = @maxe
			commit tran
		end
		
		
	end
	
	else   -- phu lai
	begin
				
		declare @sl_phulaitrong int
		set @sl_phulaitrong = (select So_PhuLaiTrong  from ChiTietLich where Ma_VanChuyen = @mavanchuyen and Ma_Xe = @maxe)
		
		if @sl_phulaitrong < 1
		begin
			set @error = N'Lỗi: đã đủ người đăng ký'
			rollback
			print @error 
		end
		
		else
		begin
			waitfor delay '00:00:05'
			set @sl_phulaitrong = @sl_phulaitrong - 1
			-- tien hanh dang ky cho nhan vien do
			insert into DangKy (Ma_VanChuyen, Ma_NV, VaiTro, Ma_Xe) values(@mavanchuyen, @manhanvien, @vaitro, @maxe)
			update ChiTietLich set So_PhuLaiTrong = @sl_phulaitrong where Ma_VanChuyen = @mavanchuyen and Ma_Xe = @maxe
			commit tran
		end
		
	end
	
GO


----------------------------------------------------------------------------
----------------------------------------------------------------------------

/****** Object:  StoredProcedure [dbo].[sp_XemLichVanChuyen_tatca]    Script Date: 09/27/2013 23:03:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

if exists(Select * from sysobjects where name = 'sp_XemLichVanChuyen_tatca')
begin
drop proc sp_XemLichVanChuyen_tatca
end
go
CREATE proc [dbo].[sp_XemLichVanChuyen_tatca]
		@error nvarchar(100) out, @slxe int out

as

begin tran

	begin tran
	
	
	if exists (select * from LichVanChuyen LVC where LVC.Gio_Di > GETDATE())
	begin
		-- set sl xe
		 
		set @slxe = (select COUNT(*)
			from ChiTietLich CTL, LichVanChuyen LVC
			where CTL.Ma_VanChuyen = LVC.Ma_VanChuyen and LVC.Gio_Di > GETDATE())
		
		waitfor delay '00:00:05'			
		-- select data
		select LVC.Ma_VanChuyen, Diem_XuatPhat,Diem_Den ,CTL.Ma_Xe, Khoang_Cach,Gio_Di, Gio_Den , CTL.So_TaiXeTrong, CTL.So_PhuLaiTrong  
			from ChiTietLich CTL, LichVanChuyen LVC, TuyenDuong TD
			where CTL.Ma_VanChuyen = LVC.Ma_VanChuyen  and LVC.Ma_TuyenDuong = TD.Ma_TuyenDuong and LVC.Gio_Di > GETDATE()
			order by Diem_XuatPhat
			commit tran
	end
	
	else
	begin
		set @error = N'Lỗi: không tồn tại lịch đi nào'
		rollback
		--print @error 
		
	end
GO

----------------------------------------------------------------------------
----------------------------------------------------------------------------

/****** Object:  StoredProcedure [dbo].[sp_XemLichVanChuen_theothoigian]    Script Date: 09/27/2013 23:02:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

if exists(Select * from sysobjects where name = 'sp_XemLichVanChuen_theothoigian')
begin
drop proc sp_XemLichVanChuen_theothoigian
end
go
CREATE proc [dbo].[sp_XemLichVanChuen_theothoigian]
		@ngaydi datetime,  @ngayden datetime, @error nvarchar(100) out, @slxe int out
		
		
as
begin tran


	
	-- kiem tra xem co ton tai lich di theo thoi gian nay ko  
	
	if exists (select * from LichVanChuyen where Gio_Di >= @ngaydi and Gio_Den <= @ngayden and Gio_Di > GETDATE())
	begin
	
		-- set sl xe
		 
		set @slxe = (select COUNT(*)
			from ChiTietLich CTL, LichVanChuyen LVC
			where CTL.Ma_VanChuyen = LVC.Ma_VanChuyen and LVC.Gio_Di >= @ngaydi and LVC.Gio_Den <= @ngayden and LVC.Gio_Di > GETDATE())
		
		
		waitfor delay '00:00:05'			
		-- select data
		
		select LVC.Ma_VanChuyen, Diem_XuatPhat,Diem_Den ,CTL.Ma_Xe, Khoang_Cach,Gio_Di, Gio_Den , CTL.So_TaiXeTrong, CTL.So_PhuLaiTrong  
			from ChiTietLich CTL, LichVanChuyen LVC, TuyenDuong TD
			where CTL.Ma_VanChuyen = LVC.Ma_VanChuyen and LVC.Gio_Di >= @ngaydi and LVC.Gio_Den <= @ngayden and LVC.Ma_TuyenDuong = TD.Ma_TuyenDuong and LVC.Gio_Di > GETDATE()
					
			order by Diem_XuatPhat
			
			commit tran
	end

	else
	begin
		set @error = N'Lỗi: không tồn tại lịch đi trong khoảng thời gian này'
		rollback
	end
GO


----------------------------------------------------------------------------
----------------------------------------------------------------------------


/****** Object:  StoredProcedure [dbo].[sp_XemLichVanChuyen_theotuyenduong]    Script Date: 09/27/2013 23:03:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

if exists(Select * from sysobjects where name = 'sp_XemLichVanChuyen_theotuyenduong')
begin
drop proc sp_XemLichVanChuyen_theotuyenduong
end

go
CREATE proc [dbo].[sp_XemLichVanChuyen_theotuyenduong]
		@matuyenduong int, @error nvarchar(100) out, @slxe int out
as
	begin tran
	
	
	-- kiem tra xem co ton tai lich di theo tuyen duong nay ko
	if exists (select * from LichVanChuyen where Ma_TuyenDuong = @matuyenduong and Gio_Di > GETDATE())
	begin
		
		
		
			-- set sl xe
		 
			set @slxe = (select COUNT(*)
				from ChiTietLich CTL, LichVanChuyen LVC, TuyenDuong TD
				where CTL.Ma_VanChuyen = LVC.Ma_VanChuyen and LVC.Ma_TuyenDuong = @matuyenduong and LVC.Ma_TuyenDuong = TD.Ma_TuyenDuong and LVC.Gio_Di > GETDATE())
					
			waitfor delay '00:00:05'			
			-- select data
		
			select LVC.Ma_VanChuyen, Diem_XuatPhat,Diem_Den ,CTL.Ma_Xe, Khoang_Cach,Gio_Di, Gio_Den , CTL.So_TaiXeTrong, CTL.So_PhuLaiTrong  
			from ChiTietLich CTL, LichVanChuyen LVC, TuyenDuong TD
			where CTL.Ma_VanChuyen = LVC.Ma_VanChuyen and LVC.Ma_TuyenDuong = @matuyenduong and LVC.Ma_TuyenDuong = TD.Ma_TuyenDuong and LVC.Gio_Di > GETDATE()
			order by Diem_XuatPhat
			
			commit tran
			
	end
	
	else
	begin
		set @error = N'Lỗi: không tồn tại lịch đi với tuyến đường này'
		rollback
		print @error 
		return
	end
GO

----------------------------------------------------------------------------
----------------------------------------------------------------------------

/****** Object:  StoredProcedure [dbo].[LoadTuyenDuong]    Script Date: 09/26/2013 00:53:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

if exists(Select * from sysobjects where name = 'LoadTuyenDuong')
begin
drop proc LoadTuyenDuong
end
go


CREATE proc [dbo].[LoadTuyenDuong]
as
begin
	
	
	-- select MaTuyenDuong, DiemXuatPhat va insert vao temp
	declare @temp table(MaTuyenDuong int, TenTuyenDuong nvarchar(50))
	
	declare diemxuatphat cursor
		for select Ma_TuyenDuong, Diem_XuatPhat from TuyenDuong
		
	open diemxuatphat
	
	declare @matuyenduong int
	declare @diemxuatphat nvarchar(50)
	
	FETCH NEXT FROM diemxuatphat INTO @matuyenduong, @diemxuatphat
	while @@FETCH_STATUS=0       
	begin
		insert into @temp(MaTuyenDuong, TenTuyenDuong) values(@matuyenduong, @diemxuatphat)
		FETCH NEXT FROM diemxuatphat INTO @matuyenduong, @diemxuatphat
	end
	
	close diemxuatphat
	DEALLOCATE diemxuatphat
	
	
	-- noi diem ket thuc vao temp
	declare diemden cursor
		for select Ma_TuyenDuong, Diem_Den from TuyenDuong
		
	open diemden
	declare @matd int
	declare @diemden nvarchar(50)
	FETCH NEXT FROM diemden INTO @matd, @diemden
	while @@FETCH_STATUS=0       
	begin
		Update @temp Set TenTuyenDuong = TenTuyenDuong + ' - ' + @diemden where MaTuyenDuong = @matd
		FETCH NEXT FROM diemden INTO @matd, @diemden
	end
	
	close diemden
	DEALLOCATE diemden
	
	-- select bang temp
	select * from @temp
end
GO

----------------------------------------------------------------------------
----------------------------------------------------------------------------